作者:郭尚刚 | 来源:互联网 | 2023-05-19 11:24
ActiveCell
ActiveWorkbook
AdvancedFilter
AutoFill
ActiveCell
- 1. 检查活动单元格是否存在
Sub
activeCell()
If
ActiveCell
Is
Nothing
Then
End If
End Sub
- 2. 通过指定偏移量设置活动单元格
Sub
offset()
ActiveCell.Offset(RowOffset:
=-
2
, ColumnOffset:
=
4
).Activate
End Sub
Offset函数的第一个参数为Row的偏移量,第二个参数为Column的偏移量(可以不指定),使用时可以直接给定值,如Offset(2, 4)。值小于0向相反方向偏移。Offset().Activate与Offset().Select在效果上等同。
- 3. 设置活动单元格的当前值
Sub
SetValue
ActiveCell.Value
=
"
Hello World!
"
End Sub
- 4. 为当前活动单元格设置公式
Sub
fomula()
ActiveCell.Formula
=
"
=SUM($G$12:$G$22)
"
End Sub
将公式的表达式直接赋值给Formula属性,公式表达式可以参考Excel中的公式菜单,如求和、计数、求平均值等。
- 5. 获取当前活动单元格的地址
Sub
selectRange()
MsgBox
ActiveCell.Address
End Sub
地址的格式如:$A$11。
- 6. 获取从当前活动单元格开始到边界单元格的区域
'
从当前单元格到最顶端
Sub
SelectUp()
Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub
'
从当前单元格到最底端
Sub
SelectDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
'
从当前单元格到最右端(等同于xlEnd)
Sub
SelectToRight()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub
'
从当前单元格到最左端
Sub
SelectToLeft()
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub
- 7. 当前活动单元格所在区域选择
Sub
SelectCurrentRegion()
ActiveCell.CurrentRegion.Select
End Sub
对CurrentRegion属性所代表的区域的说明:
CurrentRegion返回活动单元格所在的周围由空行和空列组成的单元格区域(这个似乎有点不太好理解) ,可以看下图的示例:
可以这样理解CurrentRegion属性所代表的区域,即以活动单元格为中心,它所包含的矩形区域的每一行和每一列中至少包含有一个数据,上图中的蓝色阴影区域中,无论活动单元格是哪一个,其所在的当前区域均为同一区域,如B5:D7区域中的B5和C6单元格。A4的当前区域表示为A1:D7,A8的当前区域表示为A5:D11,A12的当前区域只有它本身。
使用CurrentRegion属性相当于在Excel工作表中选择菜单“编辑-定位”命令,在弹出的“定位”对话框中单击“定位条件”按钮,然后在“定位条件”对话框中选中“当前区域”选项按钮,或者相当于使用Ctrl+Shift+*组合键。在Excel2007中,该命令在以下地方可以找到:
在下拉菜单中选择“Go To Special…” ,在对话框中选择“Current region”。
有关使用CurrentRegion的一些例子:
在下图中,要使用空白单元格上方的有数据的单元格中的数据来填充空白单元格。
代码如下,
Sub
FillBlankCells()
Worksheets(
"
sheet1
"
).Range(
"
A1
"
).CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1
=
"
=R[-1]C
"
Worksheets(
"
sheet1
"
).Range(
"
A1
"
).CurrentRegion.Value
=
Worksheets(
"
sheet1
"
).Range(
"
A1
"
).CurrentRegion.Value
End Sub
执行之后,工作表中单元格A1所在当前区域中的空白单元格被相应数据填充,如下图。
‚ 如下图,对第三列进行降序排序。
代码如下:
Sub
testSort()
Dim
rng
As
Range
Set
rng
=
Worksheets(
"
sheet1
"
).Cells(
1
,
1
).CurrentRegion
rng.Sort Key1:
=
rng.Cells(
1
,
3
), Order1:
=
xlDescending, Header:
=
xlYes
End Sub
执行之后,工作表中的数据将按照第三列的数据降序排序,如下图。
- 8. 使用SpecialCells方法
该方法用于返回与指定形态和值相符合的所有单元格,其中第一个参数为xlCellType类型所代表的常数。
xlCellTypeAllFormatConditions |
任何格式的单元格。 |
xlCellTypeAllValidation |
带数据校验的单元格。 |
xlCellTypeBlanks |
空单元格。 |
xlCellTypeComments |
包含注释的单元格。 |
xlCellTypeConstants |
包含常数的单元格。 |
xlCellTypeFormulas |
包含公式的单元格。 |
xlCellTypeLastCell |
已用范围的最后一个单元格。 |
xlCellTypeSameFormatConditions |
有相同格式的单元格。 |
xlCellTypeSameValidation |
有相同数据校验准则的单元格。 |
xlCellTypeVisible |
所有可见单元格。 |
第二个参数为可选参数。如果xlCellType为xlCellTypeConstants或xlCellTypeFormulas 之一,该参数用于确定结果中应包含哪些类型的单元格。将某几个值相加可使此方法返回多种形态的单元格。默认情况下将指定所有常数或公式,对其形态则不加类型。它可以是下列常数之一。
xlErrors
xlLogical
xlNumbers
xlTextValues
Sub
SelectActiveArea()
Range(Range(
"
A1
"
), ActiveCell.SpecialCells(xlTypeLastCell)).Select
End Sub
有关使用SpecialCells的一个例子:
将下图所示的数据按顺序存放到一个新建的工作表中,
Sub
toAcol()
Dim
newSht
As
Worksheet
Dim
Rng
As
Range
Dim
allDat
As
Range
Dim
pt
As
Range
Dim
i
As
Long
'
选择工作表中所有有内容的单元格
Set
allDat
=
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
'
新增工作表
Set
newSht
=
Worksheets.Add
'
设置新工作表中的起始位置
Set
pt
=
newSht.Range(
"
a1
"
)
For
Each
Rng In allDat.Areas
For
i
=
1
To
Rng.Cells.Count
pt
=
Rng.Cells(i)
Set
pt
=
pt.Offset(
1
,
0
)
Next
Next
'
重命名新工作表
newSht.Name
=
"
newSht
"
&
Worksheets.Count
End Sub
执行后,在名称为“newSht4”的工作表中会出现如下图所示的数据。
- 9. 通过Application.WorksheetFunction调用Proper方法
Sub
FixText()
ActiveCell.Value
=
Application.WorksheetFunction.Proper(
"
asdf
"
)
End Sub
该方法将给定的表达式中的第一个字母大写,而其余字母小写,示例中的代码将活动单元格的值设置为“Asdf”。
- 10. EntireRow和EntireColumn
Sub
SelectColumn()
ActiveCell.EntireColumn.Select
End Sub
Sub
SelectRow()
ActiveCell.EntireRow.Select
End Sub
EntireColumn用于选择当前活动单元格所在的整列,EntireRow用于选择当前活动单元格所在的整行。
- 11. 找出当前所选区域中包含最大值的单元格
Sub
GoToMax()
Dim
WorkRange
As
Range
If
TypeName
(Selection)
<>
"
Range
"
Then
Exit
Sub
If
Selection.Count
=
1
Then
Set
WorkRange
=
Cells
Else
Set
WorkRange
=
Selection
End
If
MaxVal
=
Application.Max(WorkRange)
On
Error
Resume
Next
WorkRange.Find(What:
=
MaxVal, _
After:
=
WorkRange.Range(
"
A1
"
), _
LookIn:
=
xlValues, _
LookAt:
=
xlPart, _
SearchOrder:
=
xlByRows, _
SearchDirection:
=
xlNext, MatchCase:
=
False
).Select
If
Err
<>
0
Then
MsgBox
"
Max value was not found:
"
&
MaxVal
End Sub
- 12. WarpText属性
Sub
ToggleWrapText()
If
TypeName
(Selection)
=
"
Range
"
Then
Selection.WrapText
=
Not
ActiveCell.WrapText
End
If
End Sub
WarpText属性用于指示当前活动单元格是否被设置为允许换行。
返回目录
ActiveWorkbook
- 1. 获取当前活动工作簿的名称
Sub
test()
MsgBox
ActiveWorkbook.FullName
End Sub
- 2. 打开工作表
Sub
filePath()
Dim
filePath
As
String
filePath
=
ActiveWorkbook.Path
Workbooks.Open (filePath
&
"
\
"
&
"
MyWorkbook.xls
"
)
End Sub
- 3. 保存工作表
Sub
webPage()
ActiveWorkbook.SaveAs _
Filename:
=
ActiveWorkbook.Path
&
"
\myXclfile.htm
"
, _
FileFormat:
=
xlHtml
End Sub
- 4. 预览工作表
Sub
pre()
ActiveWorkbook.WebPagePreview
End Sub
- 5. 发布Excel文件到指定的目录
Public
Sub
SaveRangeWeb()
ActiveWorkbook.PublishObjects.Add _
SourceType:
=
xlSourceRange, _
Filename:
=
ActiveWorkbook.Path
&
"
\Sample1.htm
"
, _
Sheet:
=
ActiveSheet.name, _
Source:
=
"
$A$1:$B$11
"
, _
HtmlType:
=
xlHtmlStatic
ActiveWorkbook.PublishObjects(
1
).Publish (
True
)
ActiveWorkbook.PublishObjects(
1
).AutoRepublish (
False
)
End Sub
上述代码可以将当前工作簿中所选择的区域以htm文件的格式发布到一个指定的目录中,该目录可以是本地目录,也可以是远程服务器上的目录,或者是Sharepoint中的一个特定的Folder。Publish方法的参数为True表示如果目标地址的文件存在则替换,为False表示如果目标地址的文件存在则追加。AutoRepublish方法的参数用于指示当Excel文件保存的时候是否自动重新发布。
在Excel2007中,相当于点击窗体左上角的Office按钮,选择“发布”,点击“Document Management Server”,在弹出的对话框中选择相应的格式对文档进行发布操作。
- 6. 遍历ActiveWorkbook中的表单集合
Sub
Test()
For
Each
Item In ActiveWorkbook.Sheets
Debug.Print Item.name
Next
Item
End Sub
- 7. 关闭当前工作簿
Sub
close()
ActiveWorkbook.Close SaveChanges:
=
False
End Sub
将当前工作簿关闭,SaveChanges为False表示不保存当前更改。
- 8. 保护工作簿的结构和窗体
Sub
protect()
ActiveWorkbook.Protect Password:
=
"
pass
"
, Structure:
=
True
, Windows:
=
True
End Sub
该操作相当于在Excel2007中,选择“Review”菜单,选择“Protect Workbook”,点击“Protect Structure and Windows”操作,该代码示例中给该操作设置了一个用于还原的密码。
- 9. 打印工作表
Sub
print()
ActiveWorkbook.Sheets(
1
).Printout Copies:
=
2
, Collate:
=
True
End Sub
- 10. 移除工作簿中的个人信息
Sub
remove()
ActiveWorkbook.RemovePersonalInformation
=
True
End Sub
- 11. 为工作簿设置打开密码
Sub
pass()
ActiveWorkbook.Password
=
"
pass
"
End Sub
该操作相当于在Excel2007中,点击“另存为”,在弹出的对话框中选择“工具”,点击“General Options...”,在弹出的对话框中设置用于打开工作簿的密码。
- 12. 为工作簿设置可写密码
Sub
passWrite()
ActiveWorkbook.WritePassword
=
"
pass
"
End Sub
该操作相当于在Excel2007中,点击“另存为”,在弹出的对话框中选择“工具”,点击“General Options...”,在弹出的对话框中设置可修改工作簿的密码。
- 13. 在当前工作簿中打开新窗口
Sub
new
()
ActiveWorkbook.Windows(
1
).NewWindow
End Sub
- 14. 通过编程方式查找遍历工作簿当中的所有链接
Sub
PrintSimpleLinkInfo()
Dim
avLinks
As
Variant
Dim
nIndex
As
Integer
Dim
wb
As
Workbook
Set
wb
=
ActiveWorkbook
avLinks
=
wb.LinkSources(xlExcelLinks)
If
Not
IsEmpty
(avLinks)
Then
For
nIndex
=
1
To
UBound
(avLinks)
Debug.Print
"
Link found to '
"
&
avLinks(nIndex)
&
"
'
"
Next
nIndex
Else
Debug.Print
"
The workbook '
"
&
wb.name
&
"
' doesn't have any links.
"
End
If
End Sub
xlLink为一组常量,代表了Excel工作簿中各种不同类型的链接。
xlExcelLinks |
指向Excel工作表。 |
xlOLELinks |
指向OLE数据源。 |
xlPublishers |
Macintosh使用。 |
xlSubscribers |
Macintosh使用。 |
- 15. 工作簿常用属性使用
Sub
TestPrintGeneralWBInfo()
Dim
wb
As
Workbook
Set
wb
=
ActiveWorkbook
Debug.Print
"
Name:
"
&
wb.name
Debug.Print
"
Full Name:
"
&
wb.FullName
Debug.Print
"
Code Name:
"
&
wb.CodeName
Debug.Print
"
Path:
"
&
wb.Path
If
wb.ReadOnly
Then
Debug.Print
"
The workbook has been opened as read-only.
"
Else
Debug.Print
"
The workbook is read-write.
"
End
If
If
wb.Saved
Then
Debug.Print
"
The workbook does not need to be saved.
"
Else
Debug.Print
"
The workbook should be saved.
"
End
If
End Sub
返回目录
ActiveWorksheet
- 1. 改变当前工作表的名称
Sub
changeName()
ActiveSheet.name
=
"
My Sheet
"
End Sub
- 2. 向当前工作表添加超链接
Public
Sub
AddHyperlink()
ActiveSheet.Hyperlinks.Add _
Anchor:
=
Range(
"
A1
"
), _
Address:
=
""
, _
SubAddress:
=
"
'Sheet1'!A1
"
, _
ScreenTip:
=
"
Goes to Sheet1
"
, _
TextToDisplay:
=
"
Link to Sheet1
"
End Sub
- 3. 使用Copy和Paste方法
Sub
copy()
Cells(
2
,
"
B
"
).copy
Range(
"
B2:B10
"
).Select
ActiveSheet.Paste
End Sub
单元格拷贝时会同时拷贝该单元格的内容、格式以及公式等信息。
- 4. 对工作表设置密码
Sub
protect()
ActiveWorksheet.Protect Password:
=
"
pass
"
End Sub
Sub
protects()
ActiveWorksheet.Protect Password:
=
"
pass
"
, AllowFormattingCells:
=
True
, _
AllowSorting:
=
True
End Sub
- 5. 设置工作表的DisplayPageBreaks属性
Sub
Main()
ActiveSheet.DisplayPageBreaks
=
False
ActiveSheet.DisplayPageBreaks
=
True
End Sub
DisplayPageBreaks属性用于指示是否显示工作表的分页符,如果没有安装打印机,则不能设置该属性的值。
返回目录
AdvancedFilter
- 1. 使用AdvancedFilter
Sub
UniqueCustomerRedux()
Range(
"
J1
"
).Value
=
Range(
"
D1
"
).Value
Range(
"
A1
"
).CurrentRegion.AdvancedFilter xlFilterCopy, CopyToRange:
=
Range(
"
J1
"
), Unique:
=
True
End Sub
AdvancedFilter的使用类似于在Excel2007中“Data”菜单下“Sort&Filter”分类中的“Advanced”菜单的功能,其中xlFilterAction常量用于指定如何对数据进行Filter。
返回目录
AutoFill
- 1. 使用AutoFill方法自动填充单元格
Sub
autoFill()
Range(
"
F2:F13
"
).autoFill Destination:
=
Range(
"
F2:I11
"
)
End Sub
用于从SourceRange填充数据到DestinationRange,可选参数xlAutoFillType常量用于指定填充数据的方式。数据填充过程中如果SourceRange和DestinationRange的Rows数目不一致会发生异常。
返回目录